/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.madmac.placaRede;


import org.madmac.faixaIp.FaixaIpQuery;
import org.madmac.ip.Ip;
import org.madmac.ip.IpQuery;
import org.madmac.maquina.MaquinaQuery;
import org.madmac.pessoa.PessoaQuery;
import org.madmac.setor.SetorQuery;
import org.wakeful.conn.Connection;
import org.wakeful.query.Query;
/**
 *
 * @author atila
 */
public final class PlacaRedeQuery {

	public static final String TABLE = Connection.getSchema().concat(Query.DOT).concat("placa_rede");
   public static final String ALIAS = TABLE.concat(" pr");

	private PlacaRedeQuery() {
	}
	
	public static String create() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.INSERT);
      builder.append(PlacaRedeQuery.TABLE);
      builder.append(" (mac, acesso_sem_fio, id_maquina, ip)");
      builder.append(Query.VALUES);
      builder.append("(?, ?, ?, ?)");
      return builder.toString();
   }
   
   public static String retrieve() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.SELECT);
      builder.append("#");
      builder.append(Query.FROM);
      builder.append(PlacaRedeQuery.TABLE);
      builder.append(Query.WHERE);
      builder.append("# = ?");
      return builder.toString();
   }
   
   public static String update() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.UPDATE);
      builder.append(PlacaRedeQuery.TABLE);
      builder.append(Query.SET);
      builder.append("id_maquina = ?, ");
      builder.append("ip = ?, ");
      builder.append("acesso_sem_fio = ?");
      builder.append(Query.WHERE);
      builder.append("mac = ?");
      return builder.toString();
   }

   public static String delete() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.DELETE);
      builder.append(PlacaRedeQuery.TABLE);
      builder.append(Query.WHERE);
      builder.append("mac = ?");
      return builder.toString();
   }

	public static String findAll() {
		StringBuilder builder = new StringBuilder();
      builder.append(Query.SELECT);
      builder.append("pr.mac, pr.acesso_sem_fio, pr.ip, s.nome AS setor, p.nome AS responsavel, ");
      builder.append("ma.sistema_op, ma.tombamento, f.descricao AS faixa, f.id_faixa_ip");
      builder.append(Query.FROM);
      builder.append(PlacaRedeQuery.ALIAS);
      builder.append(Query.INNER_JOIN);
      builder.append(MaquinaQuery.ALIAS);
      builder.append(Query.ON);
      builder.append("pr.id_maquina = ma.id_maquina");
      builder.append(Query.LEFT_JOIN);
      builder.append(SetorQuery.ALIAS);
      builder.append(Query.ON);
      builder.append("ma.id_setor = s.id_setor");
      builder.append(Query.LEFT_JOIN);
      builder.append(IpQuery.ALIAS);
      builder.append(Query.ON);
      builder.append("ip.ip = pr.ip");
      builder.append(Query.INNER_JOIN);
      builder.append(FaixaIpQuery.ALIAS);
      builder.append(Query.ON);
      builder.append("ip.id_faixa_ip = f.id_faixa_ip");
      builder.append(Query.LEFT_JOIN);
      builder.append(PessoaQuery.ALIAS);
      builder.append(Query.ON);
      builder.append("p.id_pessoa = ma.id_pessoa");
		return builder.toString();
	}

   public static String findByMac() {
      StringBuilder builder = new StringBuilder();
      builder.append(PlacaRedeQuery.findAll());
      builder.append(Query.WHERE);
      builder.append("pr.mac = ?");
      return builder.toString();
   }
}
